<?php
/**
* ac_db.inc.php: Database class using the PHP OCI8 extension
* @package Oracle
*/

/**
* Oracle Database access methods
* @package Oracle
* @subpackage Db
*/
class Db {
/**
* @var resource The connection resource
* @access protected
*/
protected $conn = null;
/**
* @var resource The statement resource identifier
* @access protected
*/
protected $stid = null;
/**
* @var integer The number of rows to prefetch with queries
* @access protected
*/
protected $prefetch = 100;
/**
* Constructor opens a connection to the database
* @param string $module Module text for End-to-End Application Tracing
* @param string $cid Client Identifier for End-to-End Application Tracing
*/
function __construct($module, $cid) {
	$this->conn = @oci_pconnect(SCHEMA, PASSWORD, DATABASE, CHARSET);
	if (!$this->conn) {
		$m = oci_error();
		throw new Exception('Cannot connect to database: ' . $m['message']);
	}
	// Record the "name" of the web user, the client info and the module.
	// These are used for end-to-end tracing in the DB.
	oci_set_client_info($this->conn, CLIENT_INFO);
	oci_set_module_name($this->conn, $module);
	oci_set_client_identifier($this->conn, $cid);
}
/**
* Destructor closes the statement and connection
*/
function __destruct() {
	if ($this->stid)
	oci_free_statement($this->stid);
	if ($this->conn)
	oci_close($this->conn);
}

function closeSession(){
	oci_close($this->conn);
}

function commit(){
	oci_commit($this->conn);
}

/**
* Run a SQL or PL/SQL statement
*
* Call like:
* Db::execute("insert into mytab values (:c1, :c2)",
* "Insert data", array(array(":c1", $c1, -1),
* array(":c2", $c2, -1)))
*
* For returned bind values:
* Db::execute("begin :r := myfunc(:p); end",
* "Call func", array(array(":r", &$r, 20),
* array(":p", $p, -1)))
*
* Note: this performs a commit.
*
* @param string $sql The statement to run
* @param string $action Action text for End-to-End Application Tracing
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
*/
public function execute($sql, $action, $bindvars = array()) {
	$this->stid = oci_parse($this->conn, $sql);
	if ($this->prefetch >= 0) {
	oci_set_prefetch($this->stid, $this->prefetch);
	}
	foreach ($bindvars as $bv) {
		// oci_bind_by_name(resource, bv_name, php_variable, length)
		oci_bind_by_name($this->stid, $bv[0], $bv[1], $bv[2]);
	}
	oci_set_action($this->conn, $action);
	oci_execute($this->stid); // will auto commit
}
/**
* Run a query and return all rows.
*
* @param string $sql A query to run and return all rows
* @param string $action Action text for End-to-End Application Tracing
* @param array $bindvars Binds. An array of (bv_name, php_variable, length)
* @return array An array of rows
*/
public function execFetchAll($sql, $action, $bindvars = array()) {
	$this->execute($sql, $action, $bindvars);
	oci_fetch_all($this->stid, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
	$this->stid = null; // free the statement resource
	return($res);
}

/*
* returns the content of a table
*/
public function gettablecontent($tablename) {
	$mysql = "select * from " . $tablename ;
	$myres = $this->execFetchAll($mysql, "GetTableContent");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}

/*
* returns the users, their email and password
*/
/*
public function getcustomer($e) {
	$mysql = "select password from customer where email = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetCustomer");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres[0]['PASSWORD'];
	}
	return $t;
}
*/

public function newActivity($email, $ticker, $transactionID){
	
	$mysql = "select portfolio_id from has_a_portfolio where email='$email'";
	$temp = $this->execFetchAll($mysql, "");
	$portfolioID = $temp[0]['PORTFOLIO_ID'];
	
	$c = "PORTFOLIO_ID, TICKER, TRANSACTION_ID";
	$v = "$portfolioID, $ticker, $transactionID";
	$this->doinsert("transaction",$c, $v);
}

/*
* Insert a row into a table
*/
public function doinsert($table, $colnames, $vals) {
	$mysql = "insert into " . $table . " (" .$colnames . ") values (" . $vals . ")";
	$myres = $this->execute($mysql, "doinsert");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}

public function newTransaction($quantity, $price, $buySell){
	
	//get max of current transaction ids
	//generate interest id
	$mysql = "select max(transaction_id) from transaction";
	$temp = $this->execFetchAll($mysql, "aa");
	$transactionID = $temp[0]['MAX(TRANSACTION_ID)'];
	$transactionID++;
	
	//insert in table
	$c = "TRANSACTION_ID, QUANTITY, PRICE, TDATE, BUY_OR_SELL";
	$v = "$transactionID, $quantity, $price, SYSDATE, $buySell";
	$this->doinsert("transaction",$c, $v);
	
	//return transaction id
	return $transactionID;
	
}

/*
* TRANSACTION
*/
public function gettx($e) {
	$sql1 = "select c.email, a.ticker_symbol, t.txdate, t.price, t.buyorsell ";
	$sql2 = "from customer c, has_a_portfolio h, activity a, transaction t ";
	$sql3 = "where c.user_id = h.user_id and h.portfolio_id = a.portfolio_id and ";
	$sql4 = "a.transaction_id = t.transaction_id and c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}

public function getUserStockTransactions($e, $stock) {
	$sql1 = "select a.transaction_id ";
	$sql2 = "from customer c, has_a_portfolio h, activity a ";
	$sql3 = "where c.user_id = h.user_id and h.portfolio_id = a.portfolio_id and ";
	$sql4 = "c.email = '$e' and a.ticker = '$stock'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;
	$myres = $this->execFetchAll($mysql, "GetTX");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}


public function getTransactionQuantity($e) {
	$sql1 = "select quantity ";
	$sql2 = "from transaction t ";
	$sql3 = "where t.transaction_id = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3;
	$myres = $this->execFetchAll($mysql, "GetTX");
	if (empty($myres)) {
		return null;
	} else {
		return $myres[0]['QUANTITY'];
	}
}

public function getStockName ($e) {
	$mysql = "select company_name from stock where ticker_symbol = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetstockN");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres[0]['COMPANY_NAME'];
	}
	return $t;
}

public function getCurrentStockValue ($e) {
	$mysql = "select current_value from stock where ticker_symbol = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetstockN");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres[0]['CURRENT_VALUE'];
	}
	return $t;
}


/*
* Delete a row from a table
*/
public function dodelete($table, $criteria) {
	$mysql = "delete from " . $table . " where " . $criteria ;
	$myres = $this->execute($mysql, "dodelete");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}

public function getUserPassword($e) {
	$mysql = "select password from customer where email = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetCustomer");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres[0]['PASSWORD'];
	}
	return $t;
}

/*
public function getUserInfo($e) {
	$mysql = "select * from customer where email = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetCustomer");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres;
	}
	return $t;
}
*/

public function loginUser($e, $p) {
	$myres = $this->getUserPassword($e);
	if (empty($myres)) {
		$t = null;
	} else {
		$t = ($myres == $p);
	}
	return $t;
}

public function debugQuery($email){
	
	$mysql = "select portfolio_id from has_a_portfolio where email='$email'";
	$temp = $this->execFetchAll($mysql, "");
	$portfolioID = $temp[0]['PORTFOLIO_ID'];
	
}

public function createUser($infoArray) {
	$c = "EMAIL, FIRST_NAME, LAST_NAME, PASSWORD, PHONE_NUMBER, ADDRESS, CITY, STATE, ZIP, COUNTRY, LIQUID_BALANCE";
	$v = "'{$infoArray['email']}', '{$infoArray['firstName']}', '{$infoArray['lastName']}', '{$infoArray['password1']}', {$infoArray['phoneNumber']}, '{$infoArray['address']}', '{$infoArray['city']}', '{$infoArray['state']}', '{$infoArray['zip']}', '{$infoArray['country']}', 0";
	$this->doinsert("customer",$c, $v);
	
	//also automatically create the interest list and portfolio of the user
	$interestID;
	$portfolioID;
	
	//generate interest id
	$mysql = "select max(interest_id) from interest_list";
	$temp = $this->execFetchAll($mysql, "");
	$interestID = $temp[0]['MAX(INTEREST_ID)'];
	$interestID++;
	
	//generate portfolio id
	$mysql = "select max(portfolio_id) from portfolio";
	$temp = $this->execFetchAll($mysql, "");
	$portfolioID = $temp[0]['MAX(PORTFOLIO_ID)'];
	$portfolioID++;
	
	//create user portfolio
	$c = "portfolio_id, number_of_stocks, overall_value, amount_invested, overall_change, percent_change";
	$v = "'$portfolioID', 0, 0, 0, 0, 0";
	$this->doinsert("portfolio",$c, $v);
	
	//create users interest list
	$c = "interest_id, number_of_stocks";
	$v = "'$interestID', 0";
	$this->doinsert("interest_list",$c, $v);
	
	//create has_a tables
	$c = "email, portfolio_id";
	$v = "'{$infoArray['email']}', $portfolioID";
	$this->doinsert("has_a_portfolio",$c, $v);
	
	$c = "email, interest_id";
	$v = "'{$infoArray['email']}', $interestID";
	$this->doinsert("has_a_interest",$c, $v);
}

/*change to update*/
public function updateStock($ticker, $last, $volume, $high, $low, $opening) {
	
	$query = "update stock set DAILY_HIGH=$high, DAILY_LOW=$low, CURRENT_VALUE=$last, DAILY_VOLUME=$volume, OPEN_PRICE=$opening where TICKER_SYMBOL='$ticker'";
	$this->execute($mysql, "GetCustomer");
}

public function updatePassword($e, $p) {
	$mysql = "update customer set password = '".$p . "'" . " where email = '" . $e . "'";
	$myres = $this->execute($mysql, "GetCustomer");
	if (empty($myres)) {
		return null;
	} else {
		return $myres;
	}
}


public function getShareQuantity($email, $ticker){
	
	//get portfolio id
	$sql1 = "select portfolio_id from has_a_portfolio where email='$email' ";
	$temp = $this->execFetchAll($sql1, "GetTX");
	$pID = $temp[0]['PORTFOLIO_ID'];
	
	//get all transactions of that portfolio with that stock
	$sql1 = "select transaction_id from activity where ticker_symbol='$ticker' and portfolio_id=$pID";
	$temp = $this->execFetchAll($sql1, "GetTX");
	$transactionIDS = array();
	
	//loop through temp to get values
	foreach($temp as $tableRow){
		
		//push value
		array_push($transactionIDS, $tableRow['TRANSACTION_ID']);
	}
	
	$totalAmount = 0; 
	$individualAmount;
	$temp;
	
	foreach($transactionIDS as $individualID){
		
		$sql1 = "select quantity from transaction where transaction_id=$individualID";
		$temp = $this->execFetchAll($sql1, "GetTX");
		$individualAmount = $temp[0]['QUANTITY']; 
		$totalAmount += $indvidualAmount;
	}
	
	return totalAmount;
}

public function updateUser($submittedData) {
	$sql1 = "update customer set first_name='{$submittedData['firstName']}', last_name='{$submittedData['lastName']}', ";  
	$sql2 = "address='{$submittedData['address']}', city='{$submittedData['city']}', state='{$submittedData['state']}', country='{$submittedData['country']}'";
	$sql3 = " where email='{$submittedData['email']}'";
	$sql = $sql1 . $sql2 . $sql3;
	$this->execute($sql, "GetCustomer");
}

public function getLiquidBalance($email){
	
	$sql = "select liquid_balance from customer where email='$email'";
	
	$myres = $this->execFetchAll($sql, "GetCustomer");
	return $myres[0]['LIQUID_BALANCE'];
}

public function specificStockSearch($searchTerm){
	
	//search for both matches in ticker and stock
	$query1 = "select ticker_symbol from stock where ticker_symbol like '$searchTerm'";
	$query1 = "select ticker_symbol from stock where company_name like '$searchTerm'";
	
	$temp1 = $this->execFetchAll($query1, "GetCustomer");
	$temp2 = $this->execFetchAll($query1, "GetCustomer");
	
	$result1 = array();
	$result2 = array();
	
	//loop to obtain results
	for($i = 0; $i < count($temp1) ; $i++){
		
		$result1[$i] = $temp1[$i]['TICKER_SYMBOL'];
	}
	
//loop to obtain results
	for($i = 0; $i < count($temp2) ; $i++){
		
		$result2[$i] = $temp2[$i]['TICKER_SYMBOL'];
	}
	
	//now merge the two arrrays and reutrn
	return(array_merge($result1, $result2));
}

public function setLiquidBalance($email, $balance){
	
	$sql = "update customer set liquid_balance='$balance' where email='$email'";
	$myres = $this->execute($sql, "GetCustomer");
}

public function setCurrentPortfolioValues($email, $currentValue, $overallChange, $percentChange) {
	
	//get portfolio id
	$mysql = "select portfolio_id from has_a_portfolio where email='$email'";
	$temp = $this->execFetchAll($mysql, "");
	$portfolioID = $temp[0]['PORTFOLIO_ID'];
	
	$query = "update portfolio set overall_value=$currentValue, overall_change=$overallChange, percent_change=$percentChange where portfolio_id=$portfolioID";
	$this->execute($query, "GetCustomer");
}

public function getCustomerDetails($e) {
	$mysql = "select * from customer where email = '" . $e . "'";
	$myres = $this->execFetchAll($mysql, "GetCustomer");
	if (empty($myres)) {
		$t = null;
	} else {
		$t = $myres[0];
	}
	return $t;
}

public function getPortfolioStocks ($e) {
	$sql1 = "select a.ticker_symbol ";
	$sql2 = "from customer c, has_a_portfolio h, activity a, transaction t ";
	$sql3 = "where c.email = h.email and h.portfolio_id = a.portfolio_id and ";
	$sql4 = "a.transaction_id = t.transaction_id and c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	$result = array();
	if(count($myres) > 1){
		foreach($myres as $currentTicker){
		
			if(getShareQuantity($myres[$i]['TICKER_SYMBOL']) > 0){
				array_push($result, $myres[$i]['TICKER_SYMBOL']);
			}
		}
		
		return $result;
	}
	else{
		return $result;
	}
}

public function addInterest($email, $ticker){
	
	//get interest ID from database
	$sql1 = "select interest_id from has_a_interest where email='" . $email . "'";
	
	$temp = $this->execFetchAll($sql1, "GetInterest");
	$id = $temp['0']['INTEREST_ID'];
	
	$c = "INTEREST_ID, TICKER_SYMBOL, SINCE";
	$v = "'$id', '$ticker', SYSDATE";
	$this->doinsert("Contains",$c, $v);
	
	//also, increment the counter
	$sql1 = "select number_of_stocks from interest_list where interest_id=$id";
	$temp = $this->execFetchAll($sql1, "GetTX");
	$currentCount = $temp[0]['NUMBER_OF_STOCKS'];
	
	//inrement counter
	$currentCount++;
	
	//insert again
	$mysql = "update interest_list set number_of_stocks = '".$currentCount . "'" . " where interest_id = '" . $id . "'";
	$myres = $this->execute($mysql, "GetCustomer");
}

public function getInterestStocks ($e) {
	$sql1 = "select b.ticker_symbol ";
	$sql2 = "from customer c, has_a_interest h, contains b ";
	$sql3 = "where c.email = h.email and h.interest_id = b.interest_id and ";
	$sql4 = "c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	$result = array();
	
	//loop to obtain results
	for($i = 0; $i < count($myres) ; $i++){
		
		$result[$i] = $myres[$i]['TICKER_SYMBOL'];
	}
	
	return $result;
}

public function getCurrentPortfolioValue($e) {
	$sql1 = "select p.overall_value ";
	$sql2 = "from customer c, has_a_portfolio h, portfolio p ";
	$sql3 = "where c.email = h.email and h.portfolio_id = p.portfolio_id and ";
	$sql4 = "c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	return $myres[0]['OVERALL_VALUE'];
}

public function getPortfolioAmountInvested($e) {
	$sql1 = "select p.amount_invested ";
	$sql2 = "from customer c, has_a_portfolio h, portfolio p ";
	$sql3 = "where c.email = h.email and h.portfolio_id = p.portfolio_id and ";
	$sql4 = "c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	return $myres[0]['AMOUNT_INVESTED'];
}



public function getOverallPortfolioChange($e) {
	$sql1 = "select p.overall_change ";
	$sql2 = "from customer c, has_a_portfolio h, portfolio p ";
	$sql3 = "where c.email = h.email and h.portfolio_id = p.portfolio_id and ";
	$sql4 = "c.email = '" . $e . "'";
	$mysql = $sql1 . $sql2 . $sql3 . $sql4;

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	return $myres[0]['OVERALL_CHANGE'];
}

public function getAllStocks () {
	$mysql = "select ticker_symbol from stock";

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	$result = array();
	
	//loop to obtain results
	for($i = 0; $i < count($myres) ; $i++){
		
		$result[$i] = $myres[$i]['TICKER_SYMBOL'];
	}
	
	return $result;
	
}

public function getFullStockInformation($e) {
	$mysql = "select * from stock where ticker_symbol = '" . $e ."'";

	$myres = $this->execFetchAll($mysql, "GetTX");
	
	return $myres[0];
}

}

/*
* Sets the credentials to connect to the database instance
*/
function setcredentials($u, $p, $c) {
	define('SCHEMA', $u);
	define('PASSWORD', $p);
	define('DATABASE',  $c);
	define('CHARSET', 'UTF8');
	define('CLIENT_INFO', 'Project 1 Part 3');
}

?>
